CREATE DATABASE marketing;
ALTER DATABASE marketing DEFAULT CHARSET UTF8;

USE marketing;    
DROP TABLE IF EXISTS 销售人员表;  
CREATE TABLE 销售人员表 (    
    工号 VARCHAR(20) NOT NULL,    
    部门号 VARCHAR(20) NOT NULL,    
    姓名 VARCHAR(20) NOT NULL,    
    地址 VARCHAR(20) NOT NULL,    
    电话 VARCHAR(13) NULL,     
    性别 VARCHAR(2) NOT NULL,
    销售业绩 INT NULL,          
销售额 INT NULL         
);  
ALTER TABLE 销售人员表  
ADD CONSTRAINT UQ_电话 UNIQUE (电话);    
ALTER TABLE 销售人员表    
ADD PRIMARY KEY (工号);  
 
USE marketing;
DROP TABLE IF EXISTS 销售名单表;  
CREATE TABLE 销售名单表 (    
    工号 VARCHAR(20) NOT NULL,    
    部门号 VARCHAR(20) NOT NULL,    
    姓名 VARCHAR(20) NOT NULL,    
    地址 VARCHAR(20) NOT NULL,    
    电话 VARCHAR(13) NULL,     
    性别 VARCHAR(2) NOT NULL,
    销售业绩 INT NULL,          
销售额 INT NULL         
);
ALTER TABLE 销售名单表
    ADD CONSTRAINT UQ_电话 UNIQUE (电话);
ALTER TABLE 销售名单表
    ADD PRIMARY KEY (工号);

USE marketing;    
DROP TABLE IF EXISTS 销售业绩表;  
CREATE TABLE 销售业绩表 (    
    工号 VARCHAR(20) NOT NULL,    
    部门号 VARCHAR(20) NOT NULL,    
    姓名 VARCHAR(20) NOT NULL,    
    地址 VARCHAR(20) NOT NULL,    
    电话 VARCHAR(13) NULL,     
    性别 VARCHAR(2) NOT NULL,
    销售业绩 INT NULL,          
销售额 INT NULL         
);
ALTER TABLE 销售业绩表
    ADD CONSTRAINT UQ_电话 UNIQUE (电话);
ALTER TABLE 销售业绩表
    ADD PRIMARY KEY (工号);



USE marketing;  
DROP TABLE IF EXISTS 部门信息表;  
CREATE TABLE 部门信息表 (  
    编号 INT NOT NULL,    
    名称 VARCHAR(20) NOT NULL,   
    经理 VARCHAR(20) NOT NULL,     
    人数 INT NOT NULL  
);  
ALTER TABLE 部门信息表  
    ADD PRIMARY KEY (编号);  

USE marketing;
DROP TABLE IF EXISTS 客户信息表;      
CREATE TABLE 客户信息表 (      
    编号 INT NOT NULL,      
    姓名 VARCHAR(10) NOT NULL,      
    地址 VARCHAR(50) NULL,      
    电话 VARCHAR(13) NULL  );    
ALTER TABLE 客户信息表      
ADD CONSTRAINT UQ_客户信息表_电话 UNIQUE (电话);    
ALTER TABLE 客户信息表      
ADD PRIMARY KEY (编号);

USE marketing;    
DROP TABLE IF EXISTS 深圳客户信息表;    
CREATE TABLE 深圳客户信息表 (    
    编号 INT NOT NULL,    
    姓名 VARCHAR(10) NOT NULL,    
    地址 VARCHAR(50) NULL,    
    电话 VARCHAR(13) NULL  
);    
ALTER TABLE 深圳客户信息表    
ADD CONSTRAINT UQ_深圳客户信息表_电话 UNIQUE (电话);    
ALTER TABLE 深圳客户信息表    
ADD PRIMARY KEY (编号);

USE marketing;
DROP TABLE IF EXISTS 上海客户信息表;    
CREATE TABLE 上海客户信息表 (    
    编号 INT NOT NULL,    
    姓名 VARCHAR(10) NOT NULL,    
    地址 VARCHAR(50) NULL,    
    电话 VARCHAR(13) NULL  );    
ALTER TABLE 上海客户信息表    
ADD CONSTRAINT UQ_上海客户信息表_电话 UNIQUE (电话);    
ALTER TABLE 上海客户信息表    
ADD PRIMARY KEY (编号);

USE marketing;
DROP TABLE IF EXISTS 货品信息表;    
CREATE TABLE 货品信息表 (    
    编码         INT           NOT NULL,    
    名称         VARCHAR(20)   NOT NULL,    
    库存量       INT           NULL,    
    供应商编码   INT           NULL,    
    状态         BIT           NULL DEFAULT 1,  
    售价         DECIMAL(10, 2)         NULL,    
    成本价       DECIMAL(10, 2)        NULL,    
    PRIMARY KEY (编码), 
    CONSTRAINT CK_库存量 CHECK (库存量 >= 0),    
    CONSTRAINT CK_售价 CHECK (售价 IS NULL OR 售价 > 成本价)    
);

USE marketing;  
DROP TABLE IF EXISTS 订单信息表;  
CREATE TABLE 订单信息表 (  
    订单号         INT           NOT NULL,  
    销售工号       VARCHAR(20)    NULL,  
    货品编码       INT           NOT NULL,  
    客户编号       INT           NULL,  
    数量           INT           NOT NULL,   
    订货日期       DATETIME      NULL DEFAULT CURRENT_TIMESTAMP,  
    交货日期       DATETIME      NULL,  
    PRIMARY KEY (订单号),  
    FOREIGN KEY (销售工号) REFERENCES 销售人员表(工号),  
    FOREIGN KEY (货品编码) REFERENCES 货品信息表(编码),  
    FOREIGN KEY (客户编号) REFERENCES 客户信息表(编号),  
    CONSTRAINT CK_数量 CHECK (数量 >= 0)  
);

USE marketing;
DROP TABLE IF EXISTS 供应商信息表;  
CREATE TABLE 供应商信息表 (  
    编码       INT           NOT NULL,  
    名称       VARCHAR(50)   NOT NULL,  
    联系人     VARCHAR(10)   NULL,  
    地址       VARCHAR(50)   NULL,  
    电话       VARCHAR(13)   NULL,  
    PRIMARY KEY (编码)  
);

# 销售人员表
INSERT INTO 销售人员表 (工号, 部门号, 姓名, 地址, 电话, 性别, 销售业绩, 销售额)
VALUES
    ('001', '10001', '李求一', '北京市天坛', '13552800001', '男', 11000, 100000),
    ('002', '10002', '李民', '上海市青浦区', '13838400002', '男', 12000, 125000),
    ('003', '10003', '王空', '深圳福田区', '13456700003', '女', 9500, 90000),
    ('004', '10004', '赵夏', '上海松江区', '13765400004', '女', 9000, 105000),
    ('005', '10005', '孙志成', '洛阳新安县', '13213400005', '女', 10000, 115000);

# 销售名单表
INSERT INTO 销售名单表 (工号, 部门号, 姓名, 地址, 电话, 性别, 销售业绩, 销售额)
VALUES
    ('001', '10001', '张丰', '北京市天坛', '13552800001', '男', 11000, 100000),
    ('002', '10002', '李民', '上海市青浦区', '13838400002', '男', 12000, 125000),
    ('003', '10003', '王空', '深圳福田区', '13456700003', '女', 9500, 90000),
    ('004', '10004', '赵夏', '上海松江区', '13765400004', '女', 9000, 105000),
    ('005', '10005', '孙志成', '洛阳新安县', '13213400005', '女', 10000, 115000);

# 部门信息表
INSERT INTO 部门信息表 (编号, 名称, 经理, 人数)
VALUES
    (1, '销售部', '张经理', 30),
    (2, '市场部', '钱经理', 40),
    (3, '技术部', '张经理', 45),
    (4, '人事部', '赵经理', 50),
    (5, '财务部', '孙经理', 35);

# 客户信息表
INSERT INTO 客户信息表 (编号, 姓名, 地址, 电话)
VALUES
    (1, '客户1', '北京昌平区', '12300000011'),
    (2, '客户2', '上海奉贤区', '13200000022'),
    (3, '客户3', '深圳', '12400000033'),
    (4, '客户4', '广州越秀区', '14200000044'),
    (5, '客户5', '杭州上城区', '13500000055');

# 货品信息表
INSERT INTO 货品信息表 (编码, 名称, 库存量, 供应商编码, 状态, 售价, 成本价)
VALUES
    (1, '货品A', 500, 1, 1, 110, 50),
    (2, '货品B', 300, 2, 1, 130, 100),
    (3, '货品C', 200, 1, 1, 200, 150),
    (4, '货品D', 0, 3, 1, NULL, 120),
    (5, '货品E', 300, 2, 1, 350, 250);

# 订单信息表
INSERT INTO 订单信息表 (订单号, 销售工号, 货品编码, 客户编号, 数量, 订货日期, 交货日期)
VALUES
    (1, '001', 1, 1, 9, '2023-01-01 10:00:00', '2023-01-05 10:00:00'),
    (2, '002', 2, 2, 5, '2023-01-02 12:00:00', '2023-01-07 12:00:00'),
    (3, '003', 3, 3, 1, '2023-01-03 14:00:00', '2023-01-08 14:00:00'),
    (4, '001', 4, 4, 1, '2008-01-04 09:00:00', '2008-05-08 09:00:00'),
    (5, '004', 5, 5, 20, '2023-01-05 11:00:00', '2023-01-09 11:00:00');

# 供应商信息表
INSERT INTO 供应商信息表 (编码, 名称, 联系人, 地址, 电话)
VALUES
    (1, '供应商A', '周运', '北京昌平区', '12300000011'),
    (2, '供应商B', '李民', '上海奉贤区', '13200000022'),
    (3, '供应商C', '王空', '深圳福田区', '12400000033'),
    (4, '供应商D', '赵夏', '广州越秀区', '14200000044'),
    (5, '供应商E', '孙志成', '杭州上城区', '13500000055');


DELIMITER //
CREATE PROCEDURE GetSalesPersonOrderCounts (IN dept_id INT)
BEGIN
    SELECT
        销售人员表.工号,
        销售人员表.姓名,
        销售人员表.部门号,
        COUNT(订单信息表.货品编码) AS 订单数量
    FROM
        销售人员表
            LEFT JOIN 订单信息表 ON 销售人员表.工号 = 订单信息表.销售工号
    WHERE
        销售人员表.部门号 = dept_id
    GROUP BY
        销售人员表.工号,
        销售人员表.姓名,
        销售人员表.部门号;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE simpleorder()
BEGIN
    SELECT
        客户信息表.编号,
        客户信息表.姓名,
        客户信息表.电话,
        客户信息表.地址
    FROM
        客户信息表
            JOIN
        订单信息表 ON 客户信息表.编号 = 订单信息表.客户编号;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE proc_货品(IN p_product_name VARCHAR(200))
BEGIN
    DECLARE 供应商编码 INT;
    SELECT 供应商编码 INTO 供应商编码
    FROM 货品信息表
    WHERE 名称 = p_product_name
    LIMIT 1;
    IF 供应商编码 IS NOT NULL THEN
        SELECT
            编码 AS 供应商编码,
            名称 AS 供应商名称,
            联系人,
            电话,
            地址
        FROM
            供应商信息表
        WHERE
            编码 = 供应商编码;
    ELSE
        SELECT '没有找到指定的货品。' AS message;
    END IF;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE proc_货品1(
    IN p_编码 VARCHAR(20),
    OUT p_总库存量 INT,
    OUT p_总成本 DECIMAL(10, 2)
)
BEGIN
    SELECT SUM(库存量) INTO p_总库存量
    FROM 货品信息表
    WHERE 编码 = p_编码;
    SELECT SUM(库存量 * 成本价) INTO p_总成本
    FROM 货品信息表
    WHERE 编码 = p_编码;
    IF p_总库存量 IS NULL THEN SET p_总库存量 = 0; END IF;
    IF p_总成本 IS NULL THEN SET p_总成本 = 0.00; END IF;
END //
DELIMITER ;

SELECT
	SUM(售价) AS 总值,
	AVG(售价) AS 平均价
FROM
	货品信息表
WHERE
	售价 IS NOT NULL;

SELECT	* FROM 订单信息表 WHERE 订货日期 = (SELECT MIN(订货日期) FROM 订单信息表)
OR 订货日期 = (SELECT MAX(订货日期) FROM 订单信息表);

SELECT
	COUNT(*) AS 货品数量
FROM
	货品信息表
WHERE
	售价 IS NOT NULL
AND 成本价 IS NOT NULL;

SELECT
	数量
FROM
	订单信息表
ORDER BY
	货品编码 ASC;
SELECT
	姓名,
	电话
FROM
	销售人员表;

SELECT
	编码 AS 货品编码,
	名称 AS 货品名称,
	库存量 AS 货品库存量
FROM
	货品信息表;

SELECT
	货品编码,
	数量
FROM
	订单信息表;

SELECT
	货品编码,
	数量
FROM
	订单信息表
LIMIT 3;

INSERT INTO 深圳客户信息表 SELECT
	*
FROM
	客户信息表
WHERE
	地址 LIKE '深圳';

SELECT
	*
FROM
	订单信息表
WHERE
	数量 > 10;

SELECT
	*
FROM
	订单信息表
WHERE
	订货日期 >= '2008-01-01'
AND 订货日期 <= '2008-12-31';

SELECT
	*
FROM
	销售人员表
WHERE
	姓名 = '周运'
OR 姓名 = '张零器'
OR 姓名 = '李求一';

SELECT
	*
FROM
	客户信息表
WHERE
	地址 = '深圳';

SELECT
	*
FROM
	订单信息表
WHERE
	交货日期 IS NULL;

SELECT
	*
FROM
	订单信息表
WHERE
	数量 >= 10
AND 订货日期 <= 20;

SELECT
	*
FROM
	货品信息表
ORDER BY
	售价 ASC;


SELECT
	数量
FROM
	订单信息表
WHERE
	订货日期 >= '2008-00-00'
ORDER BY
	货品编码 ASC;

SELECT
	数量
FROM
	订单信息表
WHERE
	数量 >= 2
ORDER BY
	货品编码 ASC;

SELECT
	*
FROM
	销售人员表;

SELECT
	*
FROM
	销售名单表
INNER JOIN 销售业绩表 ON 销售名单表.姓名 = 销售业绩表.姓名;

SELECT
	销售业绩
FROM
	销售名单表
INNER JOIN 销售业绩表 ON 销售名单表.姓名 = 销售业绩表.姓名
WHERE
	销售业绩 IS NOT NULL;

SELECT
	销售额
FROM
	销售名单表
INNER JOIN 销售业绩表 ON 销售名单表.姓名 = 销售业绩表.姓名
WHERE
	销售业绩表.销售额 IS NOT NULL;

SELECT
	工号,
	姓名
FROM
	销售名单表;

SELECT
	销售名单表.姓名,
	销售业绩表.销售额
FROM
	销售名单表
LEFT JOIN 销售业绩表 ON 销售名单表.姓名 = 销售业绩表.姓名;

SELECT
	数量,订货日期
FROM
	订单信息表;

SELECT
	*
FROM
	订单信息表
WHERE
	订货日期 = (
		SELECT
			MIN(订货日期)
		FROM
			订单信息表
	)
OR 订货日期 = (
	SELECT
		MAX(订货日期)
	FROM
		订单信息表
);

SELECT
	*
FROM
	订单信息表
WHERE
	货品编码 IN (
		SELECT
			编码
		FROM
			货品信息表
		JOIN (
			SELECT
				货品编码,
				SUM(数量) AS total_quantity
			FROM
				订单信息表
			GROUP BY
				货品编码
			HAVING
				total_quantity > 10
		) AS order_sums ON 货品信息表.编码 = order_sums.货品编码
	);

SELECT
	*
FROM
	销售名单表
INNER JOIN 销售业绩表 ON 销售名单表.姓名 = 销售业绩表.姓名
WHERE
	销售业绩表.销售业绩 IS NOT NULL;

SELECT *
FROM
	订单信息表
WHERE
	数量 > (
		SELECT
			MIN(数量)
		FROM
			订单信息表
	);

SELECT
	*
FROM
	订单信息表
WHERE
	数量 = (
		SELECT
			MAX(数量)
		FROM
			订单信息表
	);

SELECT
	客户编号,
	姓名,
	电话
FROM
	客户信息表
JOIN 订单信息表 ON 客户信息表.编号 = 订单信息表.客户编号
WHERE
	订单信息表.数量 > 500;

SELECT
	*
FROM
	货品信息表
WHERE
	售价 > (
		SELECT
			AVG(售价)
		FROM
			货品信息表
	);

UPDATE 货品信息表
SET 售价 = 售价 * 0.9
WHERE
	售价 > 2000;

SELECT
	NOW();



SELECT
	姓名,
	CASE
WHEN 地址 LIKE '%深圳%'
OR 地址 LIKE '%上海%' THEN
	'是深圳或上海'
ELSE
	'不是深圳或上海'
END AS location_check
FROM
	客户信息表;




# SELECT * FROM 订单信息 WHERE 数量 >= 10;
#
# SELECT * FROM 订单信息 WHERE YEAR(订货日期) = 2008;
#
# SELECT * FROM 销售人员 WHERE 姓名 IN ('周运', '张零七', '李求一');
#
# SELECT * FROM 客户信息 WHERE 地区 = '深圳';
#
# SELECT * FROM 订单信息 WHERE 交货日期 IS NULL;
#
# SELECT * FROM 订单信息 WHERE 数量 BETWEEN 10 AND 20;
#
# SELECT * FROM 货品信息 ORDER BY 售价 ASC;
#
# SELECT SUM(售价) AS 总售价， AVG(售价) AS 平均售价 FROM 货品信息；

# SELECT 销售名单.工号， 销售名单.姓名， 销售业绩.业绩
# FROM 销售名单
#          JOIN 销售业绩 ON 销售名单.工号 = 销售业绩.工号；

# SELECT 销售名单.工号， 销售名单.姓名， 销售业绩.业绩
# FROM 销售名单
#          LEFT JOIN 销售业绩 ON 销售名单.工号 = 销售业绩.工号；

# SELECT 销售名单.工号， 销售名单.姓名， 销售业绩.业绩
# FROM 销售业绩
#          RIGHT JOIN 销售名单 ON 销售名单.工号 = 销售业绩.工号；

# SELECT 销售名单.工号， 销售名单.姓名， 销售业绩.业绩
# FROM 销售名单
#          FULL JOIN 销售业绩 ON 销售名单.工号 = 销售业绩.工号；

# SELECT 货品信息.货品编号， 货品信息.货品名称， SUM(订单信息.数量) AS 销售数量， MAX(订单信息.订货日期) AS 最后订货日期
# FROM 货品信息
#          LEFT JOIN 订单信息 ON 货品信息.货品编号 = 订单信息.货品编号
# GROUP BY 货品信息.货品编号， 货品信息.货品名称；

# SELECT * FROM 订单信息
# WHERE 订货日期 = (SELECT MIN(订货日期) FROM 订单信息)
#    OR 订货日期 = (SELECT MAX(订货日期) FROM 订单信息)
# ORDER BY 订货日期；

# SELECT 货品信息.*
# FROM 货品信息
#          JOIN 订单信息 ON 货品信息.货品编号 = 订单信息.货品编号
# WHERE 订单信息.数量 > 10;

# SELECT 销售人员.*
# FROM 销售人员
#          JOIN 销售业绩 ON 销售人员.工号 = 销售业绩.工号；

# SELECT * FROM 订单信息
# WHERE 数量 > (SELECT MIN(数量) FROM 订单信息);

# SELECT * FROM 订单信息
# WHERE 数量 = (SELECT MAX(数量) FROM 订单信息);

# SELECT 客户信息.客户编号， 客户信息.姓名， 客户信息.电话， SUM(订单信息.数量 * 货品信息.售价) AS 总订货金额
# FROM 客户信息
#          JOIN 订单信息 ON 客户信息.客户编号 = 订单信息.客户编号
#          JOIN 货品信息 ON 订单信息.货品编号 = 货品信息.货品编号
# GROUP BY 客户信息.客户编号， 客户信息.姓名， 客户信息.电话
# HAVING 总订货金额 > 5000;

# SELECT * FROM 货品信息
# WHERE 售价 > (SELECT AVG(售价) FROM 货品信息);

# UPDATE 货品信息
# SET 售价 = 售价 * 0.9
# WHERE 售价 > 2000;

# SELECT GETDATE() AS 系统日期和时间， MONTH(GETDATE()) AS 月份， DATENAME(MONTH, GETDATE()) AS 月份名字；

# SELECT 销售人员.姓名， COUNT(订单信息.订单ID) AS 订单个数
# FROM 销售人员
#          JOIN 订单信息 ON 销售人员.员工ID = 订单信息.员工ID
# GROUP BY 销售人员.姓名；

# SELECT 客户ID, 城市，
#     CASE 城市
#            WHEN '北京' THEN '华北'
#            WHEN '上海' THEN '华东'
#            WHEN '广州' THEN '华南'
#            ELSE '其他'
#        END AS 地区
# FROM 客户信息；

# SELECT 销售人员.姓名， COUNT(订单信息.订单ID) AS 订单个数
# FROM 销售人员
#          JOIN 订单信息 ON 销售人员.员工ID = 订单信息.员工ID
# GROUP BY 销售人员.姓名；

# CREATE FUNCTION dbo.GetOrderCountByDepartment(@部门号 INT)
#     RETURNS @结果 TABLE
# (
#     员工ID INT,
#     订单数 INT
# )
# AS
# BEGIN
# INSERT INTO @结果 (员工ID, 订单数)
# SELECT 销售人员.员工ID, COUNT(订单信息.订单ID) AS 订单数
# FROM 销售人员
#          JOIN 订单信息 ON 销售人员.员工ID = 订单信息.员工ID
# WHERE 销售人员.部门号 = @部门号
# GROUP BY 销售人员.员工ID;
# RETURN;
# END;

# CREATE PROCEDURE simpleorder
#     AS
# BEGIN
# SELECT * FROM 订单信息；
#                   END;

# DECLARE @表名 NVARCHAR(100);
# SET @表名 = '客户信息'; -- 这里可以替换为其他表名
# EXEC sp_help @表名；

# CREATE PROCEDURE proc_货品 @货品名称 NVARCHAR(100)
# AS
# BEGIN
# SELECT 供应商表.供应商ID, 供应商表.供应商名称
# FROM 货品信息表
#          JOIN 供应商表 ON 货品信息表.供应商ID = 供应商表.供应商ID
# WHERE 货品信息表.货品名称 = @货品名称；
#     END;

# CREATE PROCEDURE proc_货品1 @输入参数 NVARCHAR(100), @输出参数1 INT OUTPUT, @输出参数2 MONEY OUTPUT
# AS
# BEGIN
# SELECT @输出参数1 = SUM(库存数量), @输出参数2 = SUM(库存数量 * 成本)
# FROM 货品表
# WHERE 类别 = @输入参数；
#     END;

# SELECT 姓名， 电话 FROM 销售人员；

# SELECT 编码 AS 货品编码， 名称 AS 货品名称， 库存量 AS 货品库存量 FROM 货品信息；

# SELECT DISTINCT 货品编号， 数量 FROM 订单信息；

# SELECT TOP 3 货品编号， 数量 FROM 订单信息；